/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package org.cjava.system.bean.report;

import java.io.Serializable;
import java.util.Calendar;
import java.util.List;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;
import javax.faces.bean.SessionScoped;
import org.cjava.system.bean.associate.BusinessAssociateBean;
import org.cjava.system.model.State;
import org.cjava.system.service.interfac.IBusinessAssociateService;
import org.cjava.system.util.Clazz;
import org.primefaces.model.chart.CartesianChartModel;
import org.primefaces.model.chart.ChartSeries;
import org.primefaces.model.chart.PieChartModel;

/**
 *
 * @author user
 */
@ManagedBean
@SessionScoped
public class ReportBean implements Serializable {

    private static final long serialVersionUID = 1L;
    @ManagedProperty("#{businessAssociateService}")
    IBusinessAssociateService businessAssociateService;
    @ManagedProperty("#{clazz}")
    Clazz clazz;
    @ManagedProperty("#{businessAssociateBean}")
    BusinessAssociateBean businessAssociateBean;
    private CartesianChartModel linearModel = new CartesianChartModel();
    private CartesianChartModel categoryModel = new CartesianChartModel();
    private PieChartModel pieChartModel;
    private Long totalCardEmited;

    private String yearReport = "" + Calendar.getInstance().get(Calendar.YEAR);

    private String month(Integer number) {
        if (number == 1) {
            return "Enero";
        } else if (number == 2) {
            return "Febrero";
        } else if (number == 3) {
            return "Marzo";
        } else if (number == 4) {
            return "Abril";
        } else if (number == 5) {
            return "Mayo";
        } else if (number == 6) {
            return "Junio";
        } else if (number == 7) {
            return "Julio";
        } else if (number == 8) {
            return "Agosto";
        } else if (number == 9) {
            return "Septiembre";
        } else if (number == 10) {
            return "Octubre";
        } else if (number == 11) {
            return "Niviembre";
        } else {
            return "Diciembre";
        }
    }

    public void initReport() throws Exception {
        pieChartModel = new PieChartModel();
        //yearReport = "" + Calendar.getInstance().get(Calendar.YEAR);
        String slq1 = "select count(b.id) from BusinessAssociate b "
                + "where b.idApertureAssociate.year=(year) and "
                + "(b.codeCard='' or b.codeCard=null)";
        String sq2l2 = "select b.idState, count(b.id) from BusinessAssociate b "
                + "where b.idApertureAssociate.year=(year) and "
                + "(b.codeCard!='' and b.codeCard!=null) group by b.idState";
        slq1 = slq1.replace("(year)", businessAssociateBean.getYearBusinessSearch() + "");
        sq2l2 = sq2l2.replace("(year)", businessAssociateBean.getYearBusinessSearch() + "");

        List<Long> listDontHaveCard = businessAssociateService.listHQL(slq1);
        List<Object[]> listHaveCard = businessAssociateService.listHQL(sq2l2);
        //      System.out.println(listDontHaveCard);
//        System.out.println(listHaveCard.get(0) + " - " +listHaveCard.get(1));
        pieChartModel.set("No Recogen", listDontHaveCard.get(0));
        totalCardEmited = listDontHaveCard.get(0);
        // System.out.println("Fis: " + totalCardEmited);
        for (Object[] ob : listHaveCard) {
            State state = (State) ob[0];
            if (state.getName().equals(Clazz._STATE._NAME._BUSINESS_ASSOCIATE_CARNET_NUEVO)) {
                pieChartModel.set("Carnet's En Uso", Long.parseLong(ob[1] + ""));
                totalCardEmited = totalCardEmited + Long.parseLong(ob[1] + "");
                //System.out.println("nect :" +totalCardEmited );
            }
            if (state.getName().equals(Clazz._STATE._NAME._BUSINESS_ASSOCIATE_CARNET_PERDIDO)) {
                pieChartModel.set("Carnet's Perdidos", Long.parseLong(ob[1] + ""));
                totalCardEmited = totalCardEmited + Long.parseLong(ob[1] + "");
                //System.out.println("nectdd :" +totalCardEmited );
            }
        }
        //   System.out.println("Total Emitidos: " + totalCardEmited);
    }

    public void searchResult() throws Exception {
        System.out.println("Buscando resultado");
        String sqlReport = "select extract(month from b.start_date) as moth,"
                + " count(extract(month from b.start_date)) as total_months"
                + " from business_associate b"
                + " where year(b.start_date)= " + yearReport
                + " group by extract(month from b.start_date)";
        List<Object[]> listReport = businessAssociateService.listSQL(sqlReport, false);
        //////////////////////////////////////////////////
        linearModel = new CartesianChartModel();

        ChartSeries boys = new ChartSeries();
        boys.setLabel("Socios " + yearReport);

        for (int i = 0; i < listReport.size(); i++) {
            boys.set(
                    month(Integer.parseInt(String.valueOf(listReport.get(i)[0]))),
                    Integer.parseInt(String.valueOf(listReport.get(i)[1])));
//            System.out.println("Mes: " + listReport.get(i)[0]);
//            System.out.println("Cuantos: " + listReport.get(i)[1]);
//            System.out.println("----------------------------------");
        }
        linearModel.addSeries(boys);

    }

    private void createLinearModel() {

        linearModel = new CartesianChartModel();

        ChartSeries boys = new ChartSeries();
        boys.setLabel("Boys");

        boys.set("2004", 120);
        boys.set("2005", 100);
        boys.set("2006", 44);
        boys.set("2007", 150);
        boys.set("2008", 25);

        ChartSeries girls = new ChartSeries();
        girls.setLabel("Girls");

        girls.set("2004", 52);
        girls.set("2005", 60);
        girls.set("2006", 110);
        girls.set("2007", 135);
        girls.set("2008", 120);

        linearModel.addSeries(boys);
        linearModel.addSeries(girls);
    }

    public void initValuesForReport() throws Exception {
        yearReport = Calendar.getInstance().get(Calendar.YEAR) + "";
        searchResult();
        // createLinearModel();
    }

    public void initValuesForComareBox() throws Exception {
//        String sql = "select min(a.year), b.id_person from business_associate b, "
//                + "aperture_associate a, person p where "
//                + "b.id_aperture_associate = a.id "
//                + "and p.id=b.id_person "
//                + "group by b.id_person";
        String sql = "select min(a.year), p.dni, p.name, p.father_name, p.mother_name, "
                + "p.email, p.photo_check, b.code_card from business_associate b, aperture_associate a, person p "
                + "where b.id_aperture_associate = a.id and p.id=b.id_person group by b.id_person";
        List<Object[]> listOb = businessAssociateService.listSQL(sql, false);
        System.out.println(listOb);
        categoryModel = new CartesianChartModel();
        ChartSeries boys = new ChartSeries();
        ChartSeries renovate = new ChartSeries();
        boys.setLabel("Socios Nuevos");
        renovate.setLabel("Renovaciones");

        State stateActive = clazz.STATE(Clazz._STATE._NAME._BUSINESS_ASSOCIATE_CARNET_NUEVO, Clazz._STATE._ENTITY._BusinessAssociate);
        //2013 - 650
        //2014 - 100
        String sqlTotal = "select min(a.year), count(*) "
                + "from business_associate b , aperture_associate a "
                + "where b.id_aperture_associate=a.id and b.id_state=" + stateActive.getId()
                + " group by b.id_aperture_associate, a.year order by a.year asc";
        List<Object[]> listYearCount = businessAssociateService.listSQL(sqlTotal, false);


        for (Object[] listYearCount1 : listYearCount) {
            int conteo = 0;
            for (Object[] listOb1 : listOb) {
                int yearActuality = (Integer) listOb1[0];
                if (yearActuality == (Integer) listYearCount1[0]) {
                    conteo++;
                }
            }
            boys.set("" + listYearCount1[0], conteo);
            int totalRenovation = Integer.parseInt("" + listYearCount1[1]) - conteo;
            renovate.set("" + listYearCount1[0], totalRenovation);
        }


        categoryModel.addSeries(boys);
        categoryModel.addSeries(renovate);

    }

    public CartesianChartModel getLinearModel() {
        return linearModel;
    }

    public void setLinearModel(CartesianChartModel linearModel) {
        this.linearModel = linearModel;
    }

    public String getYearReport() {
        return yearReport;
    }

    public Long getTotalCardEmited() {
        return totalCardEmited;
    }

    public void setYearReport(String yearReport) {
        this.yearReport = yearReport;
    }

    public PieChartModel getPieChartModel() {
        return pieChartModel;
    }

    public void setPieChartModel(PieChartModel pieChartModel) {
        this.pieChartModel = pieChartModel;
    }

    public void setClazz(Clazz clazz) {
        this.clazz = clazz;
    }

    public void setBusinessAssociateBean(BusinessAssociateBean businessAssociateBean) {
        this.businessAssociateBean = businessAssociateBean;
    }

    public void setBusinessAssociateService(IBusinessAssociateService businessAssociateService) {
        this.businessAssociateService = businessAssociateService;
    }

    public CartesianChartModel getCategoryModel() {
        return categoryModel;
    }

    public void setCategoryModel(CartesianChartModel categoryModel) {
        this.categoryModel = categoryModel;
    }

}
